package cn.com.dashihui.web.service;

import java.util.List;

import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Page;
import com.jfinal.plugin.activerecord.Record;

public class UserDataService {
	
	/**
	 * 查询每月新增会员列表（未登录  未注册 生产） 2017-11  12之前数据不精确  因为还有state=0的数据
	 * group_concat默认长度1024     字符串长度超过需修改数据库
	 */
	public Page<Record> basepage(int pageNum, int pageSize){
		String fromsql=" from t_bus_user b"
				+ " left join t_bus_user t1 on b.id=t1.id and t1.state=1"
				+ " left join t_bus_user t2 on b.id=t2.id and t2.state=2"
				+ " left join t_bus_user t3 on b.id=t3.id and t3.createteam=1"
				+ " where 1=1 "
				+ " group by DATE_FORMAT(b.createDate,'%Y-%m') order by DATE_FORMAT(b.createDate,'%Y-%m') desc";
		String select="select ifnull(count(distinct b.id),0) as usNum,DATE_FORMAT(b.createDate,'%Y-%m') creatMonth,group_concat(b.id) wxids,"
				+ "ifnull(count(distinct t1.id),0) bNum,ifnull(count(distinct t2.id),0) yNum ,ifnull(count(distinct t3.id),0) cNum ";
		Page<Record> page=Db.paginate(pageNum, pageSize, select, fromsql);
		return page;
	}
	/**
	 * 查询每月新增会员的订单列表（未登录  未注册 生产）
	 */
	public Record orderPage(String userids,String month){
		String fromsql=" from t_bus_order td "
					+" left join t_bus_order t1 on td.id=t1.id and t1.payState=2 and t1.tuistate=2 and t1.tuistate!=4" //月有效订单数
					+" left join t_bus_order t2 on td.id=t2.id and t2.userid in ("+userids+")"    //新会员订单数
				/*	+" left join t_bus_order t3 on td.id=t3.id and t3.userid not in ("+userids+")" //老会员订单数
				 * 
*/		
					+" left join t_bus_order t4 on td.id=t4.id and t4.payState=2 and t4.tuistate=0 " //常规订单数：此处订单数指“亿品直供OCT”公司下的月有效订单
					+" left join t_bus_order t5 on td.id=t5.id and t5.payState=2 and t5.tuistate=0 " //团购订单数：此处订单数指“亿品直供”公司下的月有效订单数
					/*+" left join t_bus_order t6 on td.id=t6.id and t6.payState=2 and t6.tuistate=0 and t6.storeid not in ("+prestores+")"
					+" and t6.storeid not in ("+backstores+")"*///异业订单数：此处订单数指非常规团购两个公司外的所有公司下的月有效订单数
					+" where DATE_FORMAT(td.startDate,'%Y-%m')='"+month+"'";
		
		/*String sql="select ifnull(count(td.id),0) allnum,ifnull(count(t1.id),0) validnum,ifnull(count(t2.id),0) newnum,"
				+ "ifnull(count(t3.id),0) oldnum,ifnull(count(t4.id),0) prenum,ifnull(count(t5.id),0) backnum ";*/
		String sql="select ifnull(count(td.id),0) allnum,ifnull(count(t1.id),0) validnum,ifnull(count(t2.id),0) newnum,"
				+ "ifnull(count(t4.id),0) prenum,ifnull(count(t5.id),0) backnum ";
		
		return Db.findFirst(sql+fromsql);
	}
	/**
	 * 查询订单详情
	 * params: month查询月份   preSell 常规  backSell团购      type： 0常规1团购  2异业   
	 * 如果数据总数对不上那么应该还有（退款中【paystate=2 and tuistate=1】和已过期【paystate=2 and tuistate=0 and orderState=3 】两种情况没有统计）
	 */
	public Record orderDetail(String month,String preSell,String backSell,int type){
		String sql="select ifnull(count(distinct t.id),0) allNum,DATE_FORMAT(t.startDate,'%Y-%m') omonth,ifnull(count(distinct t1.id),0) ydNum,"
				+ "ifnull(count(distinct t2.id),0) wdNum,ifnull(count(distinct t3.id),0) ytNum,ifnull(count(distinct t4.id),0) dfNum,ifnull(count(distinct t5.id),0) yqNum";
		String fromsql=" from t_bus_order t "
				+ " left join t_bus_order t1 on t.id=t1.id and t1.payState=2 and t1.orderState=2 "  //已付款
				+ " left join t_bus_order t2 on t.id=t2.id and t2.payState=2 and t2.orderState=1 and t2.tuistate=0"   //未兑换
				+ " left join t_bus_order t3 on t.id=t3.id and t3.payState=2 and t3.tuistate=2"		//已退款
				+ " left join t_bus_order t4 on t.id=t4.id and t4.payState=1 and t4.delState=0"	//待付款
				+ " left join t_bus_order t5 on t.id=t5.id and t5.payState=1 and t5.delState=1" 	//已取消
				+ " left join t_dict_store ts on t.storeid=ts.storeid "
				+ " where DATE_FORMAT(t.startDate,'%Y-%m')='"+month+"'";
		if(type==0) fromsql+=" and ts.sellerId='"+preSell+"'";
		if(type==1) fromsql+=" and ts.sellerId='"+backSell+"'";
		if(type==2) fromsql+=" and ts.sellerId!='"+preSell+"' and ts.sellerId!='"+backSell+"'";
			   fromsql+= " group by omonth order by omonth desc";
		return Db.findFirst(sql+fromsql);
	}
	/**
	 * 查询分公司详情   合并版本 因为线下订单分公司名称和线上名称有些不一致，所以会导致数据不精确（总数一样，但会把一些消费分公司统计到开卡分公司）
	 */
	public List<Record> storeDetail(String backstores,String month){
		String fromsql=" from t_dict_store ts"
						+" left join t_bus_user tu on ts.title=tu.storeName and DATE_FORMAT(tu.createDate,'%Y-%m')='"+month+"'"
						+" where ts.sellerId='"+backstores+"'"
						+" group by ts.title order by usNum desc";
		String sql="select ts.title,ifnull(count( tu.id),0) usNum ";
		return Db.find(sql+fromsql);
	}
	/**
	 * 查询商品详情   type 0常规1团购2异业
	 */
	public Page<Record> goodsDetail(int pageNum, int pageSize,String month,int type){
		String sql="select tg.name,ifnull(count(tg.name),0) usNum";
		String fromsql="from t_bus_order tor "
				+ " left join t_bus_goods tg on tor.goodsid=tg.id"
				+ " left join t_bus_user_wx tw on tor.userid=tw.id "
				+ " left join t_bus_user tu on tu.id=tw.userid " //and date_format(tu.createDate,'%Y-%m')='"+month+"'"  当月新会员购买的订单
				+ " left join t_dict_store ts on ts.storeid=tor.storeid ";
				fromsql+= " where tor.payState=2 and tor.orderState=2 and date_format(tor.startDate,'%Y-%m')='"+month+"'";
				if(type==0) fromsql+= "and ts.sellerId='SE201028362'";
				if(type==1) fromsql+= "and ts.sellerId='SE233814893'";
				if(type==2) fromsql+= "and ts.sellerId!='SE201028362' and ts.sellerId!='SE233814893'";
				fromsql+= " group by tg.name order by usNum desc";
		return Db.paginate(pageNum, pageSize, sql, fromsql);
	}
	
	/**
	 * 根据公司查询下属分公司
	 */
	public Record findStores(String sellerid){
		String sql="select group_concat(\"'\",t.storeid,\"'\") storeids from t_dict_store t where t.sellerId='"+sellerid+"'";
		return Db.findFirst(sql);
	}
	
	
}